

package com.uit.ecommerce.access.mapper;

import com.uit.ecommerce.dto.WeatherCityDTO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class WeatherCityMapper extends MapperDB{
    public WeatherCityMapper() throws Exception{
        super();
    }
private void InitWeatherCityDTOFromRs(WeatherCityDTO weatherCity, ResultSet rs) throws Exception{
        if((rs != null) && (weatherCity != null)){
           weatherCity.setCityId(rs.getString("MaTinhTp"));
           weatherCity.setCityName(rs.getString("TinhTp"));
           weatherCity.setDate(rs.getString("Ngay"));
           weatherCity.setTempMin(Float.parseFloat(rs.getString("NhietDoThapNhat")));
           weatherCity.setTempMax(Float.parseFloat(rs.getString("NhietDoCaoNhat")));
           weatherCity.setNote(rs.getString("ThongTin"));
           weatherCity.setWeather(rs.getString("DuBao"));
        }
    }
     public ArrayList<WeatherCityDTO> GetAllWeatherArea() throws Exception{
        ArrayList<WeatherCityDTO> listResult = new ArrayList<WeatherCityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select TinhTp.MaTinhTp,  TinhTp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.TinhTP, ecormerce.thoitiettinhtp where TinhTp.MaTinhTp=thoitiettinhtp.MaTinhTp");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherCityDTO temp = new WeatherCityDTO();
                InitWeatherCityDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
     public void insertWeatherCity(WeatherCityDTO cls) throws Exception{
    try{
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into ecormerce.thoitiettinhtp values('"+cls.getCityId()+"','"+cls.getDate()+"',"+cls.getTempMax()+","+cls.getTempMin()+",'"+cls.getNote()+"','"+cls.getWeather()+"')");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
}
public void DeleteWeatherCity(WeatherCityDTO cls) throws Exception{
    try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete from ecormerce.thoitiettinhtp where MaTinhTp='");
            sql.append(cls.getCityId()).append("' and Ngay='"+cls.getDate()+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
}

    public void DeleteByCityId(String cityId) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete from ecormerce.thoitiettinhtp where MaTinhTp='");
            sql.append(cityId).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
    }

public boolean  checkWeatherCityExit(WeatherCityDTO cls) throws Exception{
     try{
         StringBuffer sql = new StringBuffer();
         sql.append("select * from ecormerce.thoitiettinhtp where MaTinhTp='");
         sql.append(cls.getCityId()).append("' and Ngay='"+cls.getDate()+"'");
         PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
         ResultSet rs = stmt.executeQuery();
         if((rs != null) && (rs.next())){
             return true;//đã tồn tại
         }
     }catch(Exception ex){
         throw ex;
     }
     return  false;//chưa tồn tại
}
public ArrayList<WeatherCityDTO> GetAllWeatherCityByName(String city) throws Exception{
        ArrayList<WeatherCityDTO> listResult = new ArrayList<WeatherCityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select TinhTp.MaTinhTp,  TinhTp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.TinhTp, ecormerce.thoitiettinhtp where tinhtp.matinhtp=thoitiettinhtp.matinhtp and TinhTp='"+city+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherCityDTO temp = new WeatherCityDTO();
                InitWeatherCityDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
public ArrayList<WeatherCityDTO> GetAllWeatherCityByID(String Id) throws Exception{
        ArrayList<WeatherCityDTO> listResult = new ArrayList<WeatherCityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select tinhtp.matinhtp, tinhtp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.tinhtp, ecormerce.thoitiettinhtp where tinhtp.matinhtp=thoitiettinhtp.matinhtp and TinhTp.MaTinhTP='"+Id+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
             WeatherCityDTO temp = new WeatherCityDTO();
                InitWeatherCityDTOFromRs(temp, rs);
                listResult.add(temp);
             }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
public ArrayList<WeatherCityDTO> GetAllWeatherCityByDate(String Date) throws Exception{
        ArrayList<WeatherCityDTO> listResult = new ArrayList<WeatherCityDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select tinhtp.matinhtp,  tinhtp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.tinhtp, ecormerce.thoitiettinhtp where tinhtp.matinhtp=thoitiettinhtp.matinhtp and Ngay='"+Date+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherCityDTO temp = new WeatherCityDTO();
                InitWeatherCityDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
public WeatherCityDTO GetWeatherCityByDateAndName(String Date,String name) throws Exception{
        WeatherCityDTO Result = new WeatherCityDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select tinhtp.matinhtp,  tinhtp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.tinhtp, ecormerce.thoitiettinhtp where tinhtp.matinhtp=thoitiettinhtp.matinhtp and Ngay='"+Date+"' and TinhTp='"+name+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
               InitWeatherCityDTOFromRs(Result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return Result;
    }
public WeatherCityDTO GetWeatherCityMaxTemp(String CiTyCode) throws Exception{
        WeatherCityDTO Result = new WeatherCityDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select tinhtp.matinhtp,  tinhtp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.tinhtp, ecormerce.thoitiettinhtp where tinhtp.matinhtp=thoitiettinhtp.matinhtp and tinhtp.MaTinhTp='"+CiTyCode+"' and thoitiettinhtp.NhietDoCaoNhat=(select Max(NhietDoCaoNhat) from ecormerce.thoitiettinhtp where thoitiettinhtp.MaTinhTp='"+CiTyCode+"')");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
               InitWeatherCityDTOFromRs(Result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return Result;
    }
public WeatherCityDTO GetWeatherCityMinTemp(String CiTyCode) throws Exception{
        WeatherCityDTO Result = new WeatherCityDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select tinhtp.matinhtp,  tinhtp, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.tinhtp, ecormerce.thoitiettinhtp where tinhtp.matinhtp=thoitiettinhtp.matinhtp and tinhtp.MaTinhTp='"+CiTyCode+"' and thoitiettinhtp.NhietDoCaoNhat=(select Min(NhietDoCaoNhat) from ecormerce.thoitiettinhtp where thoitiettinhtp.MaTinhTp='"+CiTyCode+"')");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
               InitWeatherCityDTOFromRs(Result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return Result;
    }
}

